# 首先导入基础模块
import os


# 然后再导入其他模块
import logging
import requests
from lxml import etree
import time
import random
from random import randint
import threading
import mysql.connector
from datetime import datetime
from ebay import   get_ebay_data
from aliexpress import captureali

import re
import sys
from PyQt6.QtWidgets import QApplication, QMessageBox
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QPushButton
from PyQt5.QtCore import QPoint
from xrates import getrate
from PyQt5.QtWidgets import QWidget, QApplication
from PyQt5.QtCore import Qt  # 正确导入Qt
from selenium.webdriver.chrome.service import Service
from selenium import webdriver
import locale
import sys, time, random
import re
from lxml import etree
import string
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.keys import Keys
import os
from selenium.webdriver.chrome.options import Options


config = {
    "user": "root",
    "password": "Zb_200407",
    "host": "gz-cdb-d4j7h16x.sql.tencentcdb.com",
    "database": "eas",
    'port':22333
}
def getconn():
    return mysql.connector.connect(**config)

def getconn2():
    conn = pool.get_connection()
    return conn
def test1():
    sql = 'select currency_simplename,rate from rates'
    conn = getconn()
    cursor_c = conn.cursor()
    cursor_c.execute(sql)
    rates = cursor_c.fetchall()
    ratedict = []
    price = 110
    currency = 'EUR'
    for rowr in rates:
        ratedict.append({"currency_simplename": rowr[0], "rate": rowr[1]})
    if currency.upper() != 'US':
        for cur in ratedict:
            if cur['currency_simplename'].lower() == currency.lower():
                print(price)
                print(cur['currency_simplename'])
                print(cur['rate'])
                price = price / cur['rate']
                print(price)


def getskus(shoptype):
    # 读取改价产品sku。
    sql = f'select distinct sku,keywords,lowvalue from sku_keywords where {shoptype}=1'
    conn = getconn()
    cursor_a = conn.cursor()
    cursor_a.execute(sql)

    skus = cursor_a.fetchall()
    skuslist = []
    # 如果做完了改价，必须在历史表中做记录；
    for rowr in skus:
        # 默认这个sku不跟价
        intfollow=0
        keywords = rowr[1]
        sku = rowr[0]
        lowvalue = rowr[2]
        sql = f"select ifnull(price,0) from follow_price  where  itemid='{sku}' "
        cursor_b = conn.cursor()
        cursor_b.execute(sql)
        followprice = cursor_b.fetchall()
        minprice = 0.0
        if followprice:
            minprice = followprice[0][0]
            sql=f'select ifnull(toprice,0) from sku_keywords_history where sku="{sku}" order by opdate desc limit 1  '
            cursor_b.execute(sql)
            sku_keywords_history = cursor_b.fetchall()
            if not sku_keywords_history:
                intfollow=1
            else:
                skuhistoryprice=sku_keywords_history[0][0]
                if abs(minprice - 1 - skuhistoryprice) > 0.1 :
                    intfollow=1
        if minprice <= 0:
            continue
        if intfollow==1 and minprice !=0:
            skuslist.append({"sku": sku, "price": minprice - 1})
    print(skuslist)
    return skuslist
def get_itemids(conn, shoptype):
    """
    获取 ebay,ali 商品的 itemid 列表，并处理逗号分隔的情况
    """
    try:
        skuslist = []
        cursor = conn.cursor(dictionary=True)

        try:
            # 获取跟价商品
            sql =f"""     SELECT itemid, price FROM follow_price WHERE shop_type = '{shoptype}' AND itemid IS NOT NULL AND LENGTH(itemid) >5 """
            cursor.execute(sql)
            follow_items = cursor.fetchall()
            
            for item in follow_items:
                itemid = item['itemid'].replace('，', ',')
                itemid = itemid.replace(';', ',')
                minprice = item['price']
                
                # 处理逗号分隔的 itemid
                for row in itemid.split(','):
                    row = row.strip()
                    if not row:
                        continue
                        
                    try:
                        # 获取历史记录
                        history_sql =f"""
                            SELECT toprice 
                            FROM sku_keywords_history 
                            WHERE sku = {row} 
                            ORDER BY opdate DESC 
                            LIMIT 1
                        """
                        cursor.execute(history_sql)
                        history = cursor.fetchone()
                        
                        intfollow = 0
                        if not history:
                            intfollow = 1
                        else:
                            if abs(minprice - 1 - history['toprice']) > 0.1:
                                intfollow = 1
                                
                        if intfollow == 1 and minprice != 0:
                            # 检查是否已存在相同的记录
                            exists = False
                            for existing in skuslist:
                                if existing['sku'] == row and existing['price'] == minprice - 1:
                                    exists = True
                                    break
                                    
                            if not exists:
                                skuslist.append({
                                    "sku": row, 
                                    "price": minprice - 1
                                })
                                
                    except Exception as e:
                        print(f"Error processing row {row}: {str(e)}")
                        
        finally:
            cursor.close()
            
    except Exception as e:
        print(f"Error in get_itemids: {str(e)}")
        import traceback
        print(traceback.format_exc())
        
    return skuslist
def simulateelem(actions,element):
    actions.move_to_element(element)
    # 执行操作，触发鼠标悬停动作
    start_time = time.time()
    # 循环来维持悬停状态，直到达到一分钟（60秒）
    while time.time() - start_time < 2:
        # 每次循环都执行操作，保持鼠标悬停
        actions.perform()
        # 可以适当添加短暂休眠，避免过于频繁的操作（比如每0.5秒执行一次悬停操作）
        time.sleep(random.random()*0.8)


def getdriver():
    driver=None
    try:
        # logging.basicConfig(
        #     level=logging.INFO,
        #     format='%(asctime)s - %(levelname)s - %(message)s',
        #     filename='./crawl.log')

        chrome_options = Options()
        chrome_options.add_argument('--disable-dev-shm-usage')
        chrome_options.add_experimental_option("detach", True)

        chrome_options.add_argument("--no-sandbox")
        chrome_options.add_experimental_option('excludeSwitches',
                                                ['enable-automation'])
        chrome_options.add_argument('--ignore-certificate-errors')
        # chrome_options.add_argument(
        #     '--cert - pref - mappings="[{"pref": "cert","value": "./lucascert.cer"}]"'
        # )

        chrome_options.add_argument('--ignore-ssl-errors')
        # 创建一个webdriver实例
        chrome_options.add_argument('--disable-gpu-vsync')
        current_path = os.getcwd()
        # logging.info(f'current_path={current_path}')
        chromedriver_path = os.path.join(current_path, "chromedriver.exe")
        # logging.info(f'chromedriver_path={chromedriver_path}')
        service = Service(executable_path=chromedriver_path)
        # 创建 WebDriver 实例时传入 Service 对象和 ChromeOptions
        driver = webdriver.Chrome(service=service, options=chrome_options)
        ver2 = driver.capabilities['chrome']['chromedriverVersion'].split(
            ' ')[0]
        # logging.info(f'chromedriver版本{ver2}\n')
        driver.implicitly_wait(3)
        # urls = ['www.qq.com', 'www.baidu.com', 'www.1688.com']
        # urlx = random.choice(urls)
    except Exception as e:
        print('getdriver',e)
    finally:
        return driver



def parsedb(conn, sku, curprice,shoptype):
    try:
        query = f'select ifnull(toprice,0) from sku_keywords_history where sku="{sku}" order by opdate desc limit 1 '
        cursor_a = conn.cursor()
        cursor_a.execute(query)
        results_a = cursor_a.fetchall()
        fromprice = 0
        if results_a:
            fromprice = results_a[0][0]
        query = f'insert into sku_keywords_history(ebay,sku,fromprice,toprice,opdate) values(1,"{sku}",{fromprice},{curprice},now()) '
        if shoptype=='ali': 
            query = f'insert into sku_keywords_history(ali,sku,fromprice,toprice,opdate) values(1,"{sku}",{fromprice},{curprice},now()) '

        cursor_a.execute(query)
        conn.commit()
    except Exception as e:
        print('parsedb',e)
def find_element_in_frames(driver):
    # 等待页面加载完成
    try:
        WebDriverWait(driver, 10).until(
            lambda d: d.execute_script('return document.readyState') == 'complete'
        )
        
        # 打印页面源码，用于调试
        # print("Page source:", driver.page_source)
        
        # 获取所有 iframe
        iframes = driver.find_elements(By.TAG_NAME, "iframe")
        print(f"找到 {len(iframes)} 个 iframe")
        
        for iframe in iframes:
            try:
                # 打印 iframe 的属性，帮助调试
                print("iframe ID:", iframe.get_attribute("id"))
                print("iframe Name:", iframe.get_attribute("name"))
                
                # 切换到 iframe
                driver.switch_to.frame(iframe)
                
                # 查找元素
                element = driver.find_element(By.ID, "productIdSearch")
                print("找到元素!")
                return element
                
            except Exception as e:
                print(f"在当前 iframe 中查找失败: {str(e)}")
                driver.switch_to.default_content()
                continue
                
    except TimeoutException:
        print("页面加载超时")
    except Exception as e:
        print(f"发生错误: {str(e)}")
        
    return None
def click_aliproduct_id_search(driver,xpaths):
    bok=False
    try:
       # 先等待元素可见
        wait = WebDriverWait(driver, 10)
        try:
            element = wait.until(
            EC.element_to_be_clickable((By.ID, "productIdSearch")))
            element.click()
            return True

        except:
            try:# 如果常规点击失败，尝试 JavaScript 点击
                driver.execute_script("arguments[0].click();", element)
                bok=True
                return True
            except Exception as e:  
                print(e)
        # 等待点击后的效果（根据实际情况调整）
        for xpath in xpaths:
            try:
                # 如果常规点击失败，尝试 JavaScript 点击
                element = wait.until(
                EC.element_to_be_clickable((By.XPATH, xpath)))
                simulateelem(actions, element)
                element.click()
                time.sleep(0.5)
                return True
            except Exception as e:
                print(e)       
    except Exception as e:
        print(f"点击产品ID搜索失败: {str(e)}")
    return False
def parse_next_url(conn,  shoptype,driver,lockdianxiaomi):
    # conn = pool.getconn2()
    # driver = getdriver()
    actions = ActionChains(driver)
    #以下代码为阿里；
    # skulist = get_itemids(conn,shoptype)
    search_data = read_search_file()
    ebaysearch = search_data['ebaysearch']  
    alisearch = search_data['alisearch']
    # lockdianxiaomi = threading.Lock()
    # sku,price
    print('shoptype',shoptype )
    while True:
        # time.sleep(20)
        current_time_stamp = time.time()
        # 将时间戳转换为本地时间的结构体
        local_time_struct = time.localtime(current_time_stamp)
        skulist = get_itemids(conn,shoptype)
        hour = local_time_struct.tm_hour
        minute = local_time_struct.tm_min
        second = local_time_struct.tm_sec
        hrandom = random.randint(1, 30)
        print(shoptype,local_time_struct)
        b_cap = 0
        if hour >= 8:
            b_cap = 1
        # if shoptype == 'ali' and b_cap == 1:
        if shoptype == 'ali'  :
            lockdianxiaomi.acquire()
            if len(skulist) > 0:

                driver.get('https://www.dianxiaomi.com/smtProduct/index.htm')
                wait = WebDriverWait(driver, 10)  # 最多等待10秒
                print(alisearch)
                time.sleep(1)
                xpathp=['//*[@id="productIdSearch"]',
                        '/html/body/div[30]/div[2]/div[2]/div[2]/div[2]/div[2]/a[3]'
                        ]
                wait = WebDriverWait(driver, 10)  # 最多等待10秒
                for xp1 in xpathp:
                    try:
                        element = wait.until(
                        EC.presence_of_element_located((By.XPATH, xp1)))
                        # 清除元素内原有的内容
                        element.click()
                        time.sleep(1)
                        break
                    except Exception as e:
                        print('ali找itemid',  e)
                alix=['/html/body/div[30]/div[2]/div[2]/div[2]/div[2]/div[2]/a[3]']
                alix.append(alisearch)
                # find_element_in_frames(driver)
                # click_aliproduct_id_search(driver,alix)
                
                
            # element.click()
            for i, sku in enumerate(skulist):
                # 输入商品编码，进行查询
                try:
                    # 搜索内容
                    logging.info(f'{i}:1')
                    wait = WebDriverWait(driver, 10)  # 最多等待10秒

                    element = wait.until(
                        EC.presence_of_element_located((By.ID, "searchValue")))
                    # 清除元素内原有的内容
                    element.clear()
                    # 向元素内输入新的内容'hello'
                    element.send_keys(sku['sku'])
                    element.send_keys(Keys.ENTER)
                    simulateelem(actions, element)
                    logging.info(f'{i}:2')

                    # try:
                    #     close_button = driver.find_element(By.CSS_SELECTOR,
                    #                                        'button.close')
                    #     close_button.click()
                    # except Exception as e:
                    #     print(e)
                    # 点击修改按钮，弹出窗口，改价  simulateelem(actions, element)

                    element_editprice = driver.find_element(
                        By.XPATH,
                        '//*[@id="smtSysMsg"]/tr[1]/td[5]/table/tbody/tr/td[2]/span/a'
                    )
                    simulateelem(actions, element_editprice)
                    try:
                        element_editprice.click()
                    except Exception as e:
                        print(e)
                    # 输入新价格
                    # element_newprice = wait.until(
                    #     EC.presence_of_element_located((By.CLASS_NAME, 'w80')))
                    logging.info(f'{i}:3')
                    element_newprice = None
                    xpath1 = '//*[@id="smtEditPriceTable"]/table/tbody/tr/td[2]/input'
                    xpath2 = '//*[@id="smtEditPriceTable"]/div/table/tbody/tr[2]/td[4]/input'
                    try:
                        element_newprice = wait.until(
                            EC.presence_of_element_located((By.XPATH, xpath2)))
                    except Exception as e:
                        print(e)

                        element_newprice = wait.until(
                            EC.presence_of_element_located((By.XPATH, xpath1)))
                    finally:
                        simulateelem(actions, element_newprice)
                        element_newprice.click()

                    logging.info(f'{i}:4')

                    element_newprice.clear()
                    fv = sku['price']
                    element_newprice.send_keys(f"{fv}")
                    element_newprice.send_keys(Keys.ENTER)
                    time.sleep(0.5)
                    logging.info(f'{i}:45')

                    element_determine = wait.until(
                        EC.presence_of_element_located((
                            By.XPATH,
                            '//*[@id="smtEditPriceModal"]/div[2]/div/div[3]/button[1]'
                        )))
                    simulateelem(actions, element_determine)
                    logging.info(f'{i}:5')

                    element_determine.click()
                    time.sleep(2)
                    inttimes = 0
                    while True:
                        try:
                            # 使用 find_elements_by_id 方法查找元素（此处定位方式可按需替换），返回元素列表
                            element_determine = driver.find_element(
                                BY.XPATH,
                                '//*[@id="smtEditPriceModal"]/div[2]/div/div[3]/button[1]'
                            )
                            if len(element_determine) > 0:
                                print("元素仍然存在")
                            else:
                                print("元素已不存在")
                                break
                        except Exception as e:
                            print("元素已不存在")
                            time.sleep(3)
                            break
                        # 每隔1秒检查一次，你可以根据实际情况调整时间间隔
                        inttimes += 1
                        if inttimes > 5:
                            element_cancel = driver.find_element(
                                BY.XPATH,
                                '//*[@id="smtEditPriceModal"]/div[2]/div/div[3]/button[2]'
                            )
                            element_cancel.click()
                    # 处理数据库
                    parsedb(conn, sku['sku'], sku['price'],shoptype)
                except Exception as e:
                    print(e)
            lockdianxiaomi.release()
        # if shoptype == 'ebay' and b_cap == 1:

        if shoptype == 'ebay'  :
            lockdianxiaomi.acquire()    # while stop_event.is_set():
            if len(skulist) > 0:

                # 点击sku 登录密码 20241121Aa
                wait = WebDriverWait(driver, 10)  # 最多等待10秒 #登录密码 20241121Aa
                try:
                    driver.get(
                        'https://www.dianxiaomi.com/ebayProduct/index.htm?dxmState=online'
                    )
                    # driver.get(
                    #     'https://www.dianxiaomi.com/ebayProduct/index.htm?dxmState=online')
                    time.sleep(0.1)
                    element = wait.until(
                        EC.presence_of_element_located(
                            (By.XPATH, ebaysearch)))
                    
                    # 模拟点击元素,上述代码，模拟点SKU
                    element.click()
                except Exception as e:
                    print(f"出现其他异常: {e}")
            for i, sku in enumerate(skulist):
                # # for i in range(5):
                #     sku = skulist[0]
                #     sku['sku'] = 'PZTC70SX3@#14'
                #     sku['price'] = '27.31'
                try:
                    element = WebDriverWait(driver, 10).until(
                        EC.element_to_be_clickable((By.ID, "searchValue")))
                    simulateelem(actions, element)
                    element.clear()
                    # simulateelem(actions, element) #登录密码 20241121Aa
                    # 再向元素中输入内容"hello"
                    element.send_keys(sku['sku'])
                    simulateelem(actions, element)
                    element.send_keys(Keys.ENTER)
                    simulateelem(actions, element)
                    xpath_price_first = '//*[@id="ebaySysMsg"]/tr/td[5]/table/tbody/tr/td[2]/div/div'
                    # 首先，实现点击价格，让价格可以高亮度显示
                    xpath_static_price = '/html/body/div[36]/div[2]/div[2]/div[4]/div[2]/div[2]/div[1]/table/tbody/tr/td[5]/table/tbody/tr/td[2]/div/div'
                    element_price_static = wait.until(
                        EC.element_to_be_clickable(
                            (By.XPATH, xpath_static_price)))
                    simulateelem(actions, element_price_static)
                    actions.double_click(element_price_static).perform()
                    xpath_price = [
                        '/html/body/div[36]/div[2]/div[2]/div[4]/div[2]/div[2]/div[1]/table/tbody/tr[1]/td[5]/table/tbody/tr/td[2]/div/div[2]/input',
                        '/html/body/div[36]/div[2]/div[2]/div[4]/div[2]/div[2]/div[1]/table/tbody/tr/td[5]/table/tbody/tr/td[2]/div/div[2]/input',
                        '//*[@id="varintUpdateInput"]'
                    ]
                    isuccess = 0
                    for xpx in xpath_price:
                        if isuccess == 1:
                            continue
                        try:
                            element_price = wait.until(
                                EC.element_to_be_clickable((By.XPATH, xpx)))
                            #  登录密码 20241121Aa
                            simulateelem(actions, element_price)
                            actions.double_click(element_price).perform()
                            element_price.send_keys(sku['price'])
                            value1 = element_price.get_attribute('value')
                            if abs(float(value1) - float(sku['price'])) < 0.01:
                                isuccess = 1
                        except Exception as e:
                            print(f"出现其他异常: {e}")
                    # for xx in range(6):
                    #     # 这些代码，测试很有用
                    #     if isuccess == 1:
                    #         continue
                    #     user_input_xpath = input("请输入一个价格的xpath: ")
                    #     print(f"你输入的xpath字符串是: {user_input_xpath}")
                    #     wait = WebDriverWait(driver, 10)
                    #     element_price = wait.until(
                    #         EC.element_to_be_clickable(
                    #             (By.XPATH, user_input_xpath)))
                    #     #  登录密码 20241121Aa
                    #     simulateelem(actions, element_price)
                    #     actions.double_click(element_price).perform()
                    #     element_price.send_keys(sku['price'])
                    #     value1 = element_price.get_attribute('value')
                    #     if abs(float(value1) - float(sku['price'])) < 0.01:
                    #         isuccess = 1
                    #     print(value1)

                    time.sleep(0.1)
                    # 上面代码输入新价格，后面代码更新价格 登录密码 20241121Aa
                    wait = WebDriverWait(driver, 10)
                    xpath_submitprice = [
                        '//*[@id="priceBotton"]/button[1]',
                        '/html/body/div[36]/div[2]/div[2]/div[4]/div[2]/div[2]/div[1]/table/tbody/tr/td[5]/table/tbody/tr/td[2]/div/div[2]/button[1]',
                        '//*[@id="priceBotton"]',
                        '/html/body/div[36]/div[2]/div[2]/div[4]/div[2]/div[2]/div[1]/table/tbody/tr[1]/td[5]/table/tbody/tr/td[2]/div/div[2]/input'
                    ]
                    intsubmitsuccess = 0
                    for xp2 in xpath_submitprice:
                        if intsubmitsuccess == 1:
                            continue
                        try:
                            element = wait.until(
                                EC.presence_of_element_located(
                                    (By.XPATH, xp2)))
                            simulateelem(actions, element)
                            # actions.click(element).perform()
                            actions.move_to_element(element)
                            time.sleep(0.1)
                            actions.double_click(element).perform()
                            intsubmitsuccess = 1
                        except Exception as e:
                            print("元素已不存在")
                            # break
                            time.sleep(0.1)
                    while True:
                        try:
                            # 使用 find_elements_by_id 方法查找元素（此处定位方式可按需替换），返回元素列表
                            # 再去找这个更新按钮，如果找到，就不进行下一步；
                            # 如果没有找到，就进入下一步
                            for xp2 in xpath_submitprice:
                                element = driver_findelement(By.XPATH, xp2)
                                if len(element) > 0:
                                    print("元素仍然存在")
                                else:
                                    print("元素已不存在")
                                    break
                        except Exception as e:
                            print("元素已不存在")
                            time.sleep(0.1)
                            break
                    parsedb(conn, sku['sku'], sku['price'],shoptype)
                    time.sleep(0.1)

                except Exception as e:
                    print('*' * 100)
                    print(e)
                    print('*' * 100)
            lockdianxiaomi.release()
def element_to_be_clickable_and_inputable(locator):
    def _predicate(driver):
        element = EC.presence_of_element_located(locator)(driver)
        if element and element.is_displayed() and element.is_enabled() and element.tag_name in ['div','INPUT', 'TEXTAREA']:
            return element
        else:
            return False
    return _predicate
def read_search_file():
    search_data = {}
    try:
        current_dir = os.path.dirname(os.path.abspath(__file__))
        # 构建search.txt的完整路径
        full_path = os.path.join(current_dir, 'search.txt')
        with open(full_path, 'r', encoding='utf-8') as file:
            for line in file:
                # Strip whitespace and split by '='
                line = line.strip()
                if '=' in line:
                    key, value = line.split('=', 1)
                    search_data[key.strip()] = value.strip()
    except FileNotFoundError:
        print(f"File not found: {file_path}")
    except Exception as e:
        print(f"An error occurred: {e}")
    
    return search_data


if __name__ == '__main__':
# # #     print('1')
# # #     test1()
# #     # x=read_search_file()
# #     # print(x)
# #     # #     skulist=[]
    conn = getconn()
#     parsedb(conn, 'PZ3000PX2-R-SE950*10sdf', 28,'ali')
# #     # #     # parsedb(conn, 'PZ3000PX2-R-SE950*10sdf', 28)
#     # #     storelist = ['ebay', 'ali']
    storelist = ['ebay', 'ali']
    # storelist = ['ebay' ]
    # storelist = ['ali']

    for shoptype in storelist:
        skulist = get_itemids(conn,shoptype)
        print(skulist)
# #     # # # skulist = getskus(shoptype)
# #     # # # def parse_next_url(conn, driver, shoptype, stop_event):
#     # stop_event=threading.Event()
#     # # storelist = ['ebay', 'ali']
#     # # # storelist = ['ali']
#     # driver = getdriver()

    # for i,shoptype in enumerate(storelist):
    #     y=get_itemids(conn,shoptype)
    #     print(y)
        # parse_next_url(conn, driver[i], shoptype,stop_event)
